/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import Helper.CustomerHelper;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

/**
 *
 * @author KIENLT
 */
public class CustomerDAO {
    public static int Insert(Connection cnn, CustomerHelper obj) throws Exception{
        String strSQL = "INSERT INTO tblCustomer(CName, Address, Phone, Email, Gender) VALUES(?,?,?,?,?)";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getName());
        stmt.setString(2, obj.getAddress());
        stmt.setString(3, obj.getPhone());
        stmt.setString(4, obj.getEmail());
        stmt.setString(5, obj.getGender());
        return stmt.executeUpdate();
    }
    
    public static int Update(Connection cnn, CustomerHelper obj) throws Exception{
        String strSQL = "UPDATE tblCustomer SET CName=?, Address=?, Phone=?, Email=?, Gender=? WHERE CID=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getName());
        stmt.setString(2, obj.getAddress());
        stmt.setString(3, obj.getPhone());
        stmt.setString(4, obj.getEmail());
        stmt.setString(5, obj.getGender());
        stmt.setString(6, obj.getId());
        return stmt.executeUpdate();
    }
    
    public static int Delete(Connection cnn, CustomerHelper obj) throws Exception{
        String strSQL = "DELETE FROM tblCustomer WHERE CID = ?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getId());
        return stmt.executeUpdate();
    }
    
   public static ResultSet FindOne(Connection cnn, CustomerHelper obj) throws Exception {
        String strSQL = "SELECT * FROM tblCustomer WHERE CID=?";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        stmt.setString(1, obj.getId());
        return stmt.executeQuery();
    }
    
    public static ArrayList<CustomerHelper> FindAll(Connection cnn) throws Exception{
        ArrayList<CustomerHelper> arrItemList = new ArrayList<CustomerHelper>();
        CustomerHelper itemData = null;
        String strSQL = "SELECT * FROM tblCustomer where Status=1";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        ResultSet rs = stmt.executeQuery();
        while(rs.next()){
            itemData = new CustomerHelper();
            itemData.setId(rs.getString("CID"));
            itemData.setName(rs.getString("CName"));
            itemData.setEmail(rs.getString("Email"));
            itemData.setPhone(rs.getString("Phone"));
            itemData.setAddress(rs.getString("Address"));
            arrItemList.add(itemData);
        }
        return arrItemList;
    }
    
    public static ResultSet FillAll(Connection cnn) throws Exception {
        String strSQL = "SELECT * FROM tblCustomer where Status=1";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        return stmt.executeQuery();
    }
    
    public static ResultSet FillOne(Connection cnn, CustomerHelper obj) throws Exception {
        String strSQL = "SELECT * FROM tblCustomer where Status=1 and CName like '%"+obj.getName()+"%'";
        PreparedStatement stmt = cnn.prepareStatement(strSQL);
        return stmt.executeQuery();
    }
    
    public static ArrayList<CustomerHelper> Search(Connection cnn, CustomerHelper obj) throws Exception{
        ArrayList<CustomerHelper> arrCustomerList = new ArrayList<CustomerHelper>();
        String strSQL = "SELECT * FROM tblCustomer where Status=1";
        if(!obj.getName().equals("")){
            strSQL += " and CName like '%"+obj.getName()+"%'";
        }
        if(!obj.getPhone().equals("")){
            strSQL += " and Phone like '%"+obj.getPhone()+"%'";
        }
        
        Statement stmt = cnn.createStatement();
        ResultSet rs = stmt.executeQuery(strSQL);
        while(rs.next()){
            obj = new CustomerHelper();
            obj.setId(rs.getString("CID"));
            obj.setName(rs.getString("CName"));
            obj.setEmail(rs.getString("Email"));
            obj.setPhone(rs.getString("Phone"));
            obj.setAddress(rs.getString("Address"));
            arrCustomerList.add(obj);
        }
        return arrCustomerList;
    }
 
}
